﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class ModeloContabilDI
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not Page.IsPostBack Then

            If (Session("strTipoModeloContabil") = "DID") Then
                lblModuloItem.Text = "Modelo Contabil - Declaração de Importação"
            ElseIf (Session("strTipoModeloContabil") = "NFS") Then
                lblModuloItem.Text = "Modelo Contabil - Nota Fical de Serviço"
            ElseIf (Session("strTipoModeloContabil") = "CDD") Then
                lblModuloItem.Text = "Modelo Contabil - Controle de Despesa"
            ElseIf (Session("strTipoModeloContabil") = "NFE") Then
                lblModuloItem.Text = "Modelo Contabil - Nota Fiscal de Entrada"
            ElseIf (Session("strTipoModeloContabil") = "SNF") Then
                lblModuloItem.Text = "Modelo Contabil - Nota Fiscal de Saída"
            End If

            Try

                Dim strCodTipoTributacaoII As String
                Dim strCodTipoTributacaoIPI As String
                Dim strCodTipoTributacaoPIS As String
                Dim strCodTipoTributacaoCOFINS As String
                Dim strCodTipoTributacaoICMS As String
                Dim strCodTipoTributacaoICMSST As String
                Dim strDataVigencia As String

                strCodTipoTributacaoII = ""
                strCodTipoTributacaoIPI = ""
                strCodTipoTributacaoPIS = ""
                strCodTipoTributacaoCOFINS = ""
                strCodTipoTributacaoICMS = ""
                strCodTipoTributacaoICMSST = ""

                lblHolding.Text = Session("strCodigoDaHolding").ToString
                lblCia.Text = Session("strCodigoDaCia").ToString
                lblNaturezaOpercao.Text = Session("strCodNaturezaOperacao").ToString
                lblUFOrigem.Text = Session("strSiglaDaUFOrigem")
                lblUFDestino.Text = Session("strSiglaDaUFDestino")
                lblCategoriaFiscal.Text = Session("strDscCategoriaFiscal")
                lblMeioTransporte.Text = Session("strDscMeioTransporte")
                lblOrigemItem.Text = Session("intCodigoDaOrigemDoItem")
                lblDestinacaoItem.Text = Session("strDscDestinacaoItem")
                lblDataVigencia.Text = Session("strDataVigencia").ToString

                strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

                    Dim theQueryString As String = "SELECT" & _
                             " A.CodUFOrigem," & _
                             " A.CodUFDestino," & _
                             " A.CodCategoriaFiscal," & _
                             " A.CodMeioTransporte," & _
                             " A.CodDestinacaoItem," & _
                             " A.CodigoDaOrigemDoItem," & _
                             " A.DataVigencia,"

                    theQueryString = theQueryString & _
                             " A.CodTipoTributacaoII," & _
                             " A.CodTipoTributacaoIPI," & _
                             " A.CodTipoTributacaoPIS," & _
                             " A.CodTipoTributacaoCOFINS," & _
                             " A.CodTipoTributacaoICMS," & _
                             " A.CodTipoTributacaoICMSST"


                    theQueryString = theQueryString & _
                             " FROM ModeloContabilHeader A, uf B, uf C, categoriafiscal D, meiotransporte E, destinacaoitem F" & _
                             " WHERE A.CodUFOrigem = B.CodigoDaUF AND A.CodUFDestino = C.CodigoDaUF AND A.CodCategoriaFiscal = D.CodCategoriaFiscal AND A.CodMeioTransporte = E.CodMeioTransporte AND A.CodDestinacaoItem = F.CodDestinacaoItem AND A.CodigoDaHolding = '" & lblHolding.Text & "'" & _
                             " AND A.CodigoDaCia = '" & lblCia.Text & "'" & _
                             " AND B.SiglaDaUF = '" & Session("strSiglaDaUFOrigem").ToString & "'" & _
                             " AND C.SiglaDaUF = '" & Session("strSiglaDaUFDestino").ToString & "'" & _
                             " AND A.CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                             " AND D.DscCategoriaFiscal = '" & Session("strDscCategoriaFiscal").ToString & "'" & _
                             " AND E.DscMeioTransporte = '" & Session("strDscMeioTransporte").ToString & "'" & _
                             " AND F.DscDestinacaoItem = '" & Session("strDscDestinacaoItem").ToString & "'" & _
                             " AND A.CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                             " AND A.DataVigencia = " & strDataVigencia & ";"

                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString

                    Try

                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                        Do While theSqlDataReader.Read()

                            strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacaoII
                            strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacaoIPI
                            strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacaoPIS
                            strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacaoCOFINS
                            strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacaoICMS
                            strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacaoICMSST

                        Loop

                        theSqlDataReader.Close()
                    Catch ex As Exception
                        'theSqlDataReader.Close()
                        lblMsg.Text = ex.Message
                    End Try

                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoII & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoIPI & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoPIS & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoCOFINS & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoICMS & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using

                Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
                    Dim mydatatable As New DataTable
                    Dim theQueryString As String
                    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                                 " FROM TipoTributacao" & _
                                 " WHERE CodTipoTributacao  = " & strCodTipoTributacaoICMSST & ";"
                    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                    theSqlCommand.CommandText = theQueryString
                    Try
                        theSqlConnection.Open()
                        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                        If (theSqlDataReader.Read()) Then
                            lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                        End If
                        theSqlDataReader.Close()
                    Catch ex As Exception
                        lblMsg.Text = ex.Message
                    End Try
                End Using


                FillDDL()

                If (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                    ddlCodigoDaConta.Enabled = False
                    ddlDebitoCredito.Enabled = False
                    ddlCamposDisponiveis.Enabled = False
                    ddlSplit.Enabled = False
                    LoadData()
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End If

    End Sub

    Private Sub LoadData()

        Dim strDataVigencia As String
        Dim strCodTipoTributacaoII As String
        Dim strCodTipoTributacaoIPI As String
        Dim strCodTipoTributacaoPIS As String
        Dim strCodTipoTributacaoCOFINS As String
        Dim strCodTipoTributacaoICMS As String
        Dim strCodTipoTributacaoICMSST As String

        strCodTipoTributacaoII = ""
        strCodTipoTributacaoIPI = ""
        strCodTipoTributacaoPIS = ""
        strCodTipoTributacaoCOFINS = ""
        strCodTipoTributacaoICMS = ""
        strCodTipoTributacaoICMSST = ""

        lblHolding.Text = Session("strCodigoDaHolding").ToString
        lblCia.Text = Session("strCodigoDaCia").ToString
        lblNaturezaOpercao.Text = Session("strCodNaturezaOperacao").ToString
        lblUFOrigem.Text = Session("strSiglaDaUFOrigem")
        lblUFDestino.Text = Session("strSiglaDaUFDestino")
        lblCategoriaFiscal.Text = Session("strDscCategoriaFiscal")
        lblMeioTransporte.Text = Session("strDscMeioTransporte")
        lblOrigemItem.Text = Session("intCodigoDaOrigemDoItem")
        lblDestinacaoItem.Text = Session("strDscDestinacaoItem")
        lblDataVigencia.Text = Session("strDataVigencia").ToString

        strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                     " A.CodUFOrigem," & _
                     " A.CodUFDestino," & _
                     " A.CodCategoriaFiscal," & _
                     " A.CodMeioTransporte," & _
                     " A.CodDestinacaoItem," & _
                     " A.CodigoDaOrigemDoItem," & _
                     " A.DataVigencia,"

            theQueryString = theQueryString & _
                     " A.CodTipoTributacaoII," & _
                     " A.CodTipoTributacaoIPI," & _
                     " A.CodTipoTributacaoPIS," & _
                     " A.CodTipoTributacaoCOFINS," & _
                     " A.CodTipoTributacaoICMS," & _
                     " A.CodTipoTributacaoICMSST"


            theQueryString = theQueryString & _
                     " FROM ModeloContabilHeader A, uf B, uf C, categoriafiscal D, meiotransporte E, destinacaoitem F" & _
                     " WHERE A.CodUFOrigem = B.CodigoDaUF AND A.CodUFDestino = C.CodigoDaUF AND A.CodCategoriaFiscal = D.CodCategoriaFiscal AND A.CodMeioTransporte = E.CodMeioTransporte AND A.CodDestinacaoItem = F.CodDestinacaoItem AND A.CodigoDaHolding = '" & lblHolding.Text & "'" & _
                     " AND A.CodigoDaCia = '" & lblCia.Text & "'" & _
                     " AND B.SiglaDaUF = '" & Session("strSiglaDaUFOrigem").ToString & "'" & _
                     " AND C.SiglaDaUF = '" & Session("strSiglaDaUFDestino").ToString & "'" & _
                     " AND A.CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND D.DscCategoriaFiscal = '" & Session("strDscCategoriaFiscal").ToString & "'" & _
                     " AND E.DscMeioTransporte = '" & Session("strDscMeioTransporte").ToString & "'" & _
                     " AND F.DscDestinacaoItem = '" & Session("strDscDestinacaoItem").ToString & "'" & _
                     " AND A.CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND A.DataVigencia = " & strDataVigencia & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacaoII
                    strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacaoIPI
                    strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacaoPIS
                    strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacaoCOFINS
                    strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacaoICMS
                    strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacaoICMSST

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try

        End Using

        lblLinha.Text = Session("strLinhaModeloContabil")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
             " LinhaContabil, CodigoDaConta, CASE WHEN CredDeb = 1 THEN 'Crédito' ELSE 'Débito' END AS CredDeb, DescDoCampo, CodigoSplit"

            theQueryString = theQueryString & _
                     " FROM modelocontabildatail" & _
                     " WHERE CodigoDaCia = '" & lblCia.Text & "'" & _
                     " AND CodUFOrigem = '" & Session("strCodigoDaUFOrigem").ToString & "'" & _
                     " AND CodUFDestino = '" & Session("strCodigoDaUFDestino").ToString & "'" & _
                     " AND CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND CodCategoriaFiscal = '" & Session("strCodigoCategoriaFiscal").ToString & "'" & _
                     " AND CodMeioTransporte = '" & Session("strCodigoMeioTransporte").ToString & "'" & _
                     " AND CodDestinacaoItem = '" & Session("strCodigoDestinacaoItem").ToString & "'" & _
                     " AND CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND DataVigencia = " & strDataVigencia & "" & _
                     " AND CodTipoTributacaoII = '" & strCodTipoTributacaoII & "'" & _
                     " AND CodTipoTributacaoIPI = '" & strCodTipoTributacaoIPI & "'" & _
                     " AND CodTipoTributacaoPIS = '" & strCodTipoTributacaoPIS & "'" & _
                     " AND CodTipoTributacaoCOFINS = '" & strCodTipoTributacaoCOFINS & "'" & _
                     " AND CodTipoTributacaoICMS = '" & strCodTipoTributacaoICMS & "'" & _
                     " AND CodTipoTributacaoICMSST = '" & strCodTipoTributacaoICMSST & "' AND LinhaContabil = '" & lblLinha.Text & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    ddlCodigoDaConta.SelectedValue = theSqlDataReader!CodigoDaConta
                    ddlDebitoCredito.SelectedValue = theSqlDataReader!CredDeb
                    ddlCamposDisponiveis.SelectedValue = theSqlDataReader!DescDoCampo
                    ddlSplit.SelectedValue = theSqlDataReader!CodigoSplit
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

    End Sub

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaUF, DescricaoDaUF" & vbNewLine & _
                         " FROM UF" & vbNewLine & _
                         " WHERE SiglaDaUF = '" & lblUFOrigem.Text & "' ORDER BY SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblUFOrigem.Text = lblUFOrigem.Text & " - " & theSqlDataReader!DescricaoDaUF
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaUF, DescricaoDaUF" & vbNewLine & _
                         " FROM UF" & vbNewLine & _
                         " WHERE SiglaDaUF = '" & lblUFDestino.Text & "' ORDER BY SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblUFDestino.Text = lblUFDestino.Text & " - " & theSqlDataReader!DescricaoDaUF
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodNaturezaOperacao," & _
                     " DscNaturezaOperacao AS NaturezaOperacao" & vbNewLine & _
                     " FROM NaturezaOperacao" & _
                     " WHERE CodNaturezaOperacao = '" & lblNaturezaOpercao.Text & "'" & _
                     " ORDER BY CodNaturezaOperacao;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblNaturezaOpercao.Text = lblNaturezaOpercao.Text & " - " & theSqlDataReader!NaturezaOperacao
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaConta, CodigoDaConta + ' - ' + DescricaoDaConta AS DescricaoDaConta " & _
                    "FROM planodecontas " & _
                    "WHERE CodigoDaHolding = '" & lblHolding.Text & "' AND " & _
                    "CodigoDaCia = '" & lblCia.Text & "' " & _
                    "ORDER BY CodigoDaConta"


            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCodigoDaConta.DataTextField = "DescricaoDaConta"
                ddlCodigoDaConta.DataValueField = "CodigoDaConta"
                ddlCodigoDaConta.DataSource = mydatatable
                ddlCodigoDaConta.DataBind()

                ddlCodigoDaConta.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        ddlDebitoCredito.Items.Insert(0, "")
        ddlDebitoCredito.Items.Insert(1, "Crédito")
        ddlDebitoCredito.Items.Insert(2, "Débito")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT DescDoCampo " & _
                    "FROM camposdisponiveis " & _
                    "WHERE CodigoDaHolding = '" & lblHolding.Text & "' AND " & _
                    "CodigoDaCia = '" & lblCia.Text & "' AND CodigoDoCampo = '" & Session("strTipoModeloContabil") & "' " & _
                    "ORDER BY DescDoCampo"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCamposDisponiveis.DataTextField = "DescDoCampo"
                ddlCamposDisponiveis.DataValueField = "DescDoCampo"
                ddlCamposDisponiveis.DataSource = mydatatable
                ddlCamposDisponiveis.DataBind()

                ddlCamposDisponiveis.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoSplit, CAST(CodigoSplit AS VARCHAR(10)) + ' - ' + DescSplit AS DescSplit " & _
                    "FROM Split " & _
                    "WHERE CodigoDaHolding = '" & lblHolding.Text & "' AND " & _
                    "CodigoDaCia = '" & lblCia.Text & "' " & _
                    "ORDER BY CodigoSplit"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlSplit.DataTextField = "DescSplit"
                ddlSplit.DataValueField = "CodigoSplit"
                ddlSplit.DataSource = mydatatable
                ddlSplit.DataBind()

                ddlSplit.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click
        Response.Redirect("ModeloContabilLinha.aspx")
    End Sub


    Protected Sub btnConfirmar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConfirmar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.ForeColor = Drawing.Color.Blue
        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            'If Not (EditRecord(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir


            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnConfirmar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub


    Private Function DataValid() As Boolean

        DataValid = False

        If (ddlCodigoDaConta.Text = "") Then
            lblMsg.Text = "Conta Conabil deve ser selecionada."
            Exit Function
        End If

        If (ddlDebitoCredito.Text = "") Then
            lblMsg.Text = "Débito/Crédito deve ser selecionada."
            Exit Function
        End If

        If (ddlCamposDisponiveis.Text = "") Then
            lblMsg.Text = "Campo deve ser selecionada."
            Exit Function
        End If

        If (ddlSplit.Text = "") Then
            lblMsg.Text = "Split de destino deve ser selecionada."
            Exit Function
        End If

        DataValid = True

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        'Session.Add("strCodigoDaUFOrigem", theSqlDataReader!CodigoDaUF)
        'Session.Add("strCodigoDaUFDestino", theSqlDataReader!CodigoDaUF)
        'Session.Add("strDescNaturezaOperacao", theSqlDataReader!DescNaturezaOperacao)
        'Session.Add("strCodigoCategoriaFiscal", theSqlDataReader!CodCategoriaFiscal)
        'Session.Add("strCodigoMeioTransporte", theSqlDataReader!CodMeioTransporte)
        'Session.Add("strDescOrigemDoItem", theSqlDataReader!DescricaoDaOrigemDoItem)
        'Session.Add("strCodigoDestinacaoItem", theSqlDataReader!CodDestinacaoItem)


        Dim strDataVigencia As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Dim strCodTipoTributacaoII As String
        Dim strCodTipoTributacaoIPI As String
        Dim strCodTipoTributacaoPIS As String
        Dim strCodTipoTributacaoCOFINS As String
        Dim strCodTipoTributacaoICMS As String
        Dim strCodTipoTributacaoICMSST As String
        Dim strLinhaContabil As Integer

        strCodTipoTributacaoII = ""
        strCodTipoTributacaoIPI = ""
        strCodTipoTributacaoPIS = ""
        strCodTipoTributacaoCOFINS = ""
        strCodTipoTributacaoICMS = ""
        strCodTipoTributacaoICMSST = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 1, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 2, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 3, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 4, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 5, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 6, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

 
        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " LinhaContabil"

            theQueryString = theQueryString & _
                     " FROM modelocontabildatail" & _
                     " WHERE CodigoDaCia = '" & lblCia.Text & "'" & _
                     " AND CodUFOrigem = '" & Session("strCodigoDaUFOrigem").ToString & "'" & _
                     " AND CodUFDestino = '" & Session("strCodigoDaUFDestino").ToString & "'" & _
                     " AND CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND CodCategoriaFiscal = '" & Session("strCodigoCategoriaFiscal").ToString & "'" & _
                     " AND CodMeioTransporte = '" & Session("strCodigoMeioTransporte").ToString & "'" & _
                     " AND CodDestinacaoItem = '" & Session("strCodigoDestinacaoItem").ToString & "'" & _
                     " AND CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND DataVigencia = " & strDataVigencia & "" & _
                     " AND CodTipoTributacaoII = '" & strCodTipoTributacaoII & "'" & _
                     " AND CodTipoTributacaoIPI = '" & strCodTipoTributacaoIPI & "'" & _
                     " AND CodTipoTributacaoPIS = '" & strCodTipoTributacaoPIS & "'" & _
                     " AND CodTipoTributacaoCOFINS = '" & strCodTipoTributacaoCOFINS & "'" & _
                     " AND CodTipoTributacaoICMS = '" & strCodTipoTributacaoICMS & "'" & _
                     " AND CodTipoTributacaoICMSST = '" & strCodTipoTributacaoICMSST & "' ORDER BY LinhaContabil DESC;"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strLinhaContabil = theSqlDataReader!LinhaContabil + 1
                Else
                    strLinhaContabil = 1
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "INSERT INTO modelocontabildatail" & _
                     " (" & _
                     " CodigoDaHolding," & _
                     " CodigoDaCia," & _
                     " CodUFOrigem," & _
                     " CodUFDestino," & _
                     " CodNaturezaOperacao," & _
                     " CodCategoriaFiscal," & _
                     " CodMeioTransporte," & _
                     " CodDestinacaoItem," & _
                     " CodigoDaOrigemDoItem," & _
                     " DataVigencia,"

            theQueryString = theQueryString & _
                     " CodTipoTributacaoII," & _
                     " CodTipoTributacaoIPI," & _
                     " CodTipoTributacaoPIS," & _
                     " CodTipoTributacaoCOFINS," & _
                     " CodTipoTributacaoICMS," & _
                     " CodTipoTributacaoICMSST," & _
                     " TipoModeloContabil, LinhaContabil, CodigoDaConta, CredDeb, DescDoCampo, CodigoSplit," & _
                     " CreateUser," & _
                     " Created" & _
                     " )"

            theQueryString = theQueryString & _
                     " VALUES" & _
                     " (" & _
                     " '" & Session("strCodigoDaHolding") & "'," & _
                     " '" & Session("strCodigoDaCia") & "'," & _
                     " '" & Session("strCodigoDaUFOrigem") & "'," & _
                     " '" & Session("strCodigoDaUFDestino") & "'," & _
                     " '" & Session("strCodNaturezaOperacao") & "'," & _
                     " '" & Session("strCodigoCategoriaFiscal") & "'," & _
                     " '" & Session("strCodigoMeioTransporte") & "'," & _
                     " '" & Session("strCodigoDestinacaoItem") & "'," & _
                     " '" & Session("intCodigoDaOrigemDoItem") & "'," & _
                     " " & strDataVigencia & ","

            theQueryString = theQueryString & _
                     " '" & strCodTipoTributacaoII & "'," & _
                     " '" & strCodTipoTributacaoIPI & "'," & _
                     " '" & strCodTipoTributacaoPIS & "'," & _
                     " '" & strCodTipoTributacaoCOFINS & "'," & _
                     " '" & strCodTipoTributacaoICMS & "'," & _
                     " '" & strCodTipoTributacaoICMSST & "'," & _
                     " '" & Session("strTipoModeloContabil") & "', " & strLinhaContabil & ", '" & ddlCodigoDaConta.SelectedValue & "', '" & ddlDebitoCredito.SelectedIndex & "', '" & ddlCamposDisponiveis.SelectedValue & "', '" & ddlSplit.SelectedValue & "'," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        Dim strDataVigencia As String
        
        Dim strCodTipoTributacaoII As String
        Dim strCodTipoTributacaoIPI As String
        Dim strCodTipoTributacaoPIS As String
        Dim strCodTipoTributacaoCOFINS As String
        Dim strCodTipoTributacaoICMS As String
        Dim strCodTipoTributacaoICMSST As String
        
        strCodTipoTributacaoII = ""
        strCodTipoTributacaoIPI = ""
        strCodTipoTributacaoPIS = ""
        strCodTipoTributacaoCOFINS = ""
        strCodTipoTributacaoICMS = ""
        strCodTipoTributacaoICMSST = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 1, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 2, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 3, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 4, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 5, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 6, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        DeleteRecord = False

        strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM modelocontabildatail" & _
                     " WHERE CodigoDaCia = '" & lblCia.Text & "'" & _
                     " AND CodUFOrigem = '" & Session("strCodigoDaUFOrigem").ToString & "'" & _
                     " AND CodUFDestino = '" & Session("strCodigoDaUFDestino").ToString & "'" & _
                     " AND CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND CodCategoriaFiscal = '" & Session("strCodigoCategoriaFiscal").ToString & "'" & _
                     " AND CodMeioTransporte = '" & Session("strCodigoMeioTransporte").ToString & "'" & _
                     " AND CodDestinacaoItem = '" & Session("strCodigoDestinacaoItem").ToString & "'" & _
                     " AND CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND DataVigencia = " & strDataVigencia & "" & _
                     " AND CodTipoTributacaoII = '" & strCodTipoTributacaoII & "'" & _
                     " AND CodTipoTributacaoIPI = '" & strCodTipoTributacaoIPI & "'" & _
                     " AND CodTipoTributacaoPIS = '" & strCodTipoTributacaoPIS & "'" & _
                     " AND CodTipoTributacaoCOFINS = '" & strCodTipoTributacaoCOFINS & "'" & _
                     " AND CodTipoTributacaoICMS = '" & strCodTipoTributacaoICMS & "'" & _
                     " AND CodTipoTributacaoICMSST = '" & strCodTipoTributacaoICMSST & "' AND LinhaContabil = '" & lblLinha.Text & "'"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
